package jdbc.dao;

import jdbc.domain.User;
import jdbc.test.JDBCTest;
import utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.PropertyPermission;

/**
 * Created by Administrator on 2017/9/13.
 */
public class UserDao {
    public List<User> findAll(){
        Connection conn= JDBCUtils.getConnection();
        Statement statement = null;
        ResultSet result = null;
        List<User> list=new ArrayList<>();
        try {
            statement = conn.createStatement();
            String sql = "select * from user";
            result = statement.executeQuery(sql);
            while (result.next()){
                User user=new User();
                user.setId(result.getInt(1));
                user.setUsername(result.getString(2));
                user.setPassword(result.getString(3));
                user.setEmail(result.getString(4));
                user.setPhone(result.getInt(5));
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;

    }
    public boolean insert(User user){
        Connection conn= JDBCUtils.getConnection();
        String sql = "insert into user(id,username,password,email,phone) values(?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            ps.setString(4,user.getEmail());
            ps.setInt(5,user.getPhone());
            if (ps.executeUpdate()==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    public boolean update(User user){
        Connection conn= JDBCUtils.getConnection();
        String sql = "update user set username=?,password=?,email=?,phone=? where id=?";
        PreparedStatement ps = null;
        try {

            ps=conn.prepareStatement(sql);
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            ps.setString(3,user.getEmail());
            ps.setInt(4,user.getPhone());
            ps.setInt(5,user.getId());
            if (ps.executeUpdate()==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    public boolean delete(int id){
        Connection conn= JDBCUtils.getConnection();
        String sql = "delete from user where id=?";
        PreparedStatement ps = null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            int num = ps.executeUpdate();
            if (num==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    public User findById(int id){
        Connection conn= JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet result = null;
        User user=new User();
        try {
            String sql = "select * from user where id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            result = ps.executeQuery();
            while (result.next()){
                user.setId(result.getInt(1));
                user.setUsername(result.getString(2));
                user.setPassword(result.getString(3));
                user.setEmail(result.getString(4));
                user.setPhone(result.getInt(5));
            }
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
